IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'MODULE_DEPENDENCE')
BEGIN
    DROP TABLE [dbo].[MODULE_DEPENDENCE]
END

GO

CREATE TABLE [dbo].[MODULE_DEPENDENCE] ( 
	[ID]			    INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [DEPENDENT_ID]      INT            NOT NULL,
    [FROM_ID]           INT            NOT NULL,
    [DEPENDENCE_TYPE]   VARCHAR(10)    NOT NULL CONSTRAINT [CK_MODULE_DEPENDENCE_DEPENDENCE_TYPE]
            CHECK NOT FOR REPLICATION (DEPENDENCE_TYPE IN ('Mandatory', 'Additional'))
            DEFAULT 'Additional'

    CONSTRAINT [PK_MODULE_DEPENDENCE_ID] PRIMARY KEY CLUSTERED ( 
        [ID] 
    ) ON [PRIMARY],
    
    CONSTRAINT [FK_MODULE_DEPENDENCE_DEPENDENT_ID] FOREIGN KEY ( 
        [DEPENDENT_ID]
    ) REFERENCES [dbo].[EDUCATION_MODULE] (
        [ID]
    ),
    
    CONSTRAINT [FK_MODULE_DEPENDENCE_FROM_ID] FOREIGN KEY ( 
        [FROM_ID]
    ) REFERENCES [dbo].[EDUCATION_MODULE] (
        [ID]
    )
    
) ON [PRIMARY]  

CREATE INDEX [IDX_MODULE_DEPENDENCE_DEPENDENT_ID] on [dbo].[MODULE_DEPENDENCE] ([DEPENDENT_ID]) 

GO